import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class ExcelTransformer {

    private void run() {
        List<ExportData> allExportData = new ArrayList<>();

        FileInputStream io = null;
        Workbook inWorkbook = null;
        FileOutputStream out = null;
        Workbook outWorkbook = null;

        try {
            io = new FileInputStream("import/export.xlsx");
            inWorkbook = WorkbookFactory.create(io);
            Sheet inSheet = inWorkbook.getSheetAt(0);
            int rowCount = inSheet.getPhysicalNumberOfRows();

            for (int i = 1; i < rowCount; i++) {
                Row row = inSheet.getRow(i);
                ExportData exportData = new ExportData();

                Integer activityId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue();
                exportData.setActivityId(activityId);

                String activityName = row.getCell(1).getStringCellValue().trim();
                exportData.setActivityName(activityName);

                String activityCategory = row.getCell(2).getStringCellValue();
                exportData.setActivityCategory(activityCategory);

                String commission = row.getCell(3).getStringCellValue();
                exportData.setCommission(commission);

                String settlementPeriod = row.getCell(4).getStringCellValue();
                exportData.setSettlementPeriod(settlementPeriod);

                String linkType = row.getCell(5).getStringCellValue();
                exportData.setLinkType(linkType);

                String textLinkContent = row.getCell(6).getStringCellValue();
                exportData.setTextLinkContent(textLinkContent);

                String imageLinkUrl = row.getCell(7).getStringCellValue();
                exportData.setImageLinkUrl(imageLinkUrl);

                String targetUrl = row.getCell(8).getStringCellValue();
                exportData.setTargetUrl(targetUrl);

                boolean canModifyUrl = "yes".equals(row.getCell(9).getStringCellValue());
                exportData.setCanModifyUrl(canModifyUrl);

                String feedbackTag = row.getCell(10).getStringCellValue();
                exportData.setFeedbackTag(feedbackTag);

                String shortLink = row.getCell(11).getStringCellValue();
                exportData.setShortLink(shortLink);

                String encryptLink = row.getCell(12).getStringCellValue();
                exportData.setEncryptLink(encryptLink);

                String plainLink = row.getCell(13).getStringCellValue();
                exportData.setPlainLink(plainLink);

                allExportData.add(exportData);
            }

            allExportData.sort(Comparator.comparing(ExportData::getActivityName));

            outWorkbook = new XSSFWorkbook();
            Sheet outSheet = outWorkbook.createSheet("brand");

            String[] titles = new String[17];
            titles[0] = "ID";
            titles[1] = "活动ID";
            titles[2] = "活动名称";
            titles[3] = "活动分类";
            titles[4] = "佣金";
            titles[5] = "结算周期";
            titles[6] = "PC网站";
            titles[7] = "M网站";
            titles[8] = "允许修改";
            titles[9] = "短链接";
            titles[10] = "短链接M";
            titles[11] = "密文链接";
            titles[12] = "密文链接M";
            titles[13] = "明文链接";
            titles[14] = "明文链接M";
            titles[15] = "品牌IMG";
            titles[16] = "品类IMG";

            Row titleRow = outSheet.createRow(0);

            for (int i = 0; i < titles.length; i++) {
                Cell cell = titleRow.createCell(i);
                cell.setCellValue(titles[i]);
            }

            Map<Integer, String> categories = new HashMap<>();

            for (int i = 0; i < allExportData.size(); i++) {
                ExportData exportData = allExportData.get(i);

                Row dataRow = outSheet.createRow(i + 1);

                Cell idCell = dataRow.createCell(0);
                idCell.setCellValue(i + 1);

                Cell activityIdCell = dataRow.createCell(1);
                activityIdCell.setCellValue(exportData.getActivityId());

                Cell activityNameCell = dataRow.createCell(2);
                activityNameCell.setCellValue(exportData.getActivityName().replaceAll("(?i)cps", ""));

                Cell activityCategoryCell = dataRow.createCell(3);
                String categoryName = exportData.getActivityCategory();
                activityCategoryCell.setCellValue(categoryName);

                int categoryId = 0;
                for (Map.Entry<Integer, String> category : categories.entrySet()) {
                    int id = category.getKey();
                    String name = category.getValue();

                    if (categoryName.equals(name)) {
                        categoryId = id;
                        break;
                    }
                }

                if (categoryId == 0) {
                    categories.put(categories.size() + 1, categoryName);
                }

                Cell commissionCell = dataRow.createCell(4);
                commissionCell.setCellValue(exportData.getCommission());

                Cell settlementPeriodCell = dataRow.createCell(5);
                settlementPeriodCell.setCellValue(exportData.getSettlementPeriod());

                Cell pcUrlCell = dataRow.createCell(6);
                String url = exportData.getTargetUrl();
                pcUrlCell.setCellValue(url.substring(url.indexOf("http")));

                Cell mUrlCell = dataRow.createCell(7);
                mUrlCell.setCellValue("");

                Cell canModifyUrlCell = dataRow.createCell(8);
                canModifyUrlCell.setCellValue(exportData.isCanModifyUrl());

                Cell shortLinkCell = dataRow.createCell(9);
                shortLinkCell.setCellValue(exportData.getShortLink());

                Cell shortLinkCellM = dataRow.createCell(10);
                shortLinkCellM.setCellValue("");

                Cell encryptLinkCell = dataRow.createCell(11);
                encryptLinkCell.setCellValue(exportData.getEncryptLink());

                Cell encryptLinkCellM = dataRow.createCell(12);
                encryptLinkCellM.setCellValue("");

                Cell plainLinkCell = dataRow.createCell(13);
                plainLinkCell.setCellValue(exportData.getPlainLink());

                Cell plainLinkCellM = dataRow.createCell(14);
                plainLinkCellM.setCellValue("");

                Cell brandImgCell = dataRow.createCell(15);
                brandImgCell.setCellValue(String.format("../images/brand/brand.%04d.jpg", i + 1));

                Cell categoryImgCell = dataRow.createCell(16);
                categoryImgCell.setCellValue("");
            }

            Sheet categorySheet = outWorkbook.createSheet("category");

            String[] categoryTitles = new String[3];
            categoryTitles[0] = "ID";
            categoryTitles[1] = "名称";
            categoryTitles[2] = "LOGO_URL";

            Row categoryRow = categorySheet.createRow(0);

            for (int i = 0; i < categoryTitles.length; i++) {
                Cell cell = categoryRow.createCell(i);
                cell.setCellValue(categoryTitles[i]);
            }

            int count = 0;
            for (Map.Entry<Integer, String> category : categories.entrySet()) {
                Row dataRow = categorySheet.createRow(count + 1);

                int id = category.getKey();
                String name = category.getValue();

                Cell idCell = dataRow.createCell(0);
                idCell.setCellValue(id);

                Cell nameCell = dataRow.createCell(1);
                nameCell.setCellValue(name);

                Cell urlCell = dataRow.createCell(2);
                urlCell.setCellValue(String.format("../images/category/category.%03d.png", id));

                count++;
            }

            out = new FileOutputStream("brand+category.xlsx");
            outWorkbook.write(out);
        } catch (IOException | InvalidFormatException ex) {
            ex.printStackTrace();
        } finally {
            if (inWorkbook != null) {
                try {
                    inWorkbook.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
            if (io != null) {
                try {
                    io.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
            if (outWorkbook != null) {
                try {
                    outWorkbook.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public static void main(String[] args) {
        ExcelTransformer app = new ExcelTransformer();
        app.run();
    }
}
